Alteryxウィークリーチャレンジ#3上級[正規表現]やってみた！

アルテリックス？アルタリクス？どちらでもわたしは一向にかまわんッッ！の國崎です。

Alteryxウィークリーチャレンジ#3をやってみたので、そちらの解説記事をご紹介します。
今回のチャレンジテーマは「#3:日付の解析」です。

今回のチャレンジでやること

お題

データセットには、テキスト内に日付を埋め込んだテキストフィールドが含まれています。 問題は、以下のように日付がいくつかの異なる方法で表されていることです。

• 16-APR-2005
• Nov 16, 1900
• 4-SEP-00
• Jan 5 2000

目標は、テキストフィールドに含まれる日付が入力された新しい日付/時刻フィールドを作成することです。また、すべての書式が同じになるように日付を標準化する必要があります。

引用元：#3:日付の解析

テキスト文から色々な文字列で含まれている日付を抽出して新しいカラムに反映する作業です。

用意されたCSVデータ

1カラム17行のテキストフィールドのデータ。

ゴール

テキストフィールドに含まれた異なる形式の日付をyyyy-mm-ddに変換して新しいカラムを生成する。

レッツチャレンジ！

正規表現で日付部分の文字列を抽出する

正規表現を使い各パターンが異なる日付の文字列を抽出する構文を記載します。
テキストフィールドの文章を確認したところ、以下5パターンの日付の文字列が確認できました。

• dd-mmm-yyyy
• dd-mmm-yy
• mmm d yyyy
• mmm dd, yyyy
• d-mmm-yy

上記5パターンの文字列から日付部分を抜き出すため、以下の構文を記載します。

.*(\d\d-[[:alpha:]]+-\d{4}).*|
.*(\d\d-[[:alpha:]]+-\d\d).*|
.*\s([[:alpha:]]+\s\d\s\d+).*|
.*\s([[:alpha:]]+\s\d\d,\s\d+).*|
.*(\d-[[:alpha:]]+-\d+).*

出力方式は解析にして実行します。

すると以下のように該当の日付部分が各カラムに抜き出されました。

フォーミュラで抽出した文字列を整形しカラムを作成する

フォーミュラを使って以下2つの作業を行います。

• 正規表現で出力された各カラムの日付をyyyy-mm-ddにする
• yyyy-mm-ddに変換された各カラムのセルを1つのカラムにまとめる

それぞれの作業について解説します。

正規表現で出力された各カラムの日付をyyyy-mm-ddにする

各カラムごとに文字列のパターンが違うので、それぞれに適した構文を書きます。
ちなみにフォーミュラでは設定の左下の「+」でカラムごとの構文を追加していくことができます。

// RegExOut1の場合
DateTimeFormat(DateTimeParse([RegExOut1], "%d-%b-%Y"), "%Y-%m-%d")

// RegExOut2の場合
IIF(
    DateTimeYear(DateTimeParse([RegExOut2], "%d-%b-%y")) < 24,
    DateTimeFormat(DateTimeAdd(DateTimeParse([RegExOut2], "%d-%b-%y"), 100, "years"), "%Y-%m-%d"),
    DateTimeFormat(DateTimeParse([RegExOut2], "%d-%b-%y"), "%Y-%m-%d")
)

// RegExOut3の場合
DateTimeFormat(DateTimeParse([RegExOut3], "%b %d %Y"), "%Y-%m-%d")

// RegExOut4の場合
DateTimeFormat(DateTimeParse([RegExOut4], "%b %d, %Y"), "%Y-%m-%d")

// RegExOut5の場合
IIF(
    DateTimeYear(DateTimeParse([RegExOut5], "%d-%b-%y")) < 24,
    DateTimeFormat(DateTimeAdd(DateTimeParse([RegExOut5], "%d-%b-%y"), 100, "years"), "%Y-%m-%d"),
    DateTimeFormat(DateTimeParse([RegExOut5], "%d-%b-%y"), "%Y-%m-%d")
)

これで以下のようにバラバラだった日付の文字列がyyyy-mm-ddに統一されます。

yyyy-mm-ddに変換された各カラムのセルを1つのカラムにまとめる

上記でyyyy-mm-ddに統一された各カラムのセルを新しい1つのカラムを作成し、そこにまとめます。
各カラムにはNULLがあるのでNULLを除く必要があるため、以下の構文を書きます。

IIF(IsNull([RegExOut1]), "", [RegExOut1]) +
IIF(IsNull([RegExOut2]), "", [RegExOut2]) +
IIF(IsNull([RegExOut3]), "", [RegExOut3]) +
IIF(IsNull([RegExOut4]), "", [RegExOut4]) +
IIF(IsNull([RegExOut5]), "", [RegExOut5])

新しいカラム名を「DataTime_Out」と決めて、上記実行すると以下のように1つのカラムに各日付をまとめることができました。

セレクトで不要なカラムの削除とデータ型の変更をする

セレクトを使って必要なカラムのみ残します。
今回はテキストフィールドが入っていた「Field_1」と、日付を抽出してyyyy-mm-ddに変換した「DataTime_Out」のカラムのみの形式にします。

合わせて「DataTime_Out」をDate型に変換します。

これで実行すると今回のゴールのデータになりました。

最終のワークフローはこんな感じ。

まとめ

今回上級の初チャレンジでしたが、まぁまぁ難しかったですね。
正規表現を普段扱っていないので途中発狂しそうになりましたが、なんとかできました。

こういった感じで文字列からも決まったパターンがあれば複数の条件指定でデータを抜き出すことができるのがAlteryxの特徴だと思います。